package com.authine.cloudpivot.ext.utils;

import com.alibaba.cola.dto.RequestContext;
import com.authine.cloudpivot.ext.dto.InquiryGoodDto;
import com.authine.cloudpivot.ext.dto.OrderVendorGoodDto;
import com.authine.mvp.app.launcher.domain.utills.ApplicationContextUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

@Slf4j
public class InquiryGoodJdbc {

    private static JdbcTemplate jdbcTemplate = ApplicationContextUtils.getBean(JdbcTemplate.class);


    public static Integer getCountSql (InquiryGoodDto good){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT distinct ");
        sql.append(getCommonConditionSql());
        sql = getCommonOrderSql(good, sql);
        log.info("sql:{}",sql);
        return jdbcTemplate.queryForList(sql.toString()).size();
    }

    private static StringBuffer getCommonOrderSql(InquiryGoodDto good, StringBuffer sql) {
        sql.append ("\t FROM\n" +
                "\tsourcingcenter.i_sourcingcenter_t_inquiry_goods xjsp\n" +
                "\tLEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry xjd ON xjsp.inquiry_id = xjd.id and xjd.deleted = 0\n" +
                "\tLEFT JOIN sourcingcenter.i_sourcingcenter_t_quotation_goods stqg ON stqg.inquiry_goods_id = xjsp.id and stqg.deleted=0\n" +
                "\tLEFT JOIN operation1center.i_operation1center_t_platform_grant_user zckh on zckh.organization_id = xjd.organization_id\n" +
                "\twhere xjsp.deleted=0 ") ;
        getQueryCondition(good,sql);
        return  sql;
    }
    private static StringBuffer getCommonClarifyOrderSql(InquiryGoodDto good, StringBuffer sql) {
        sql.append (" FROM\n" +
                "\tsourcingcenter.i_sourcingcenter_t_inquiry_goods xjsp\n" +
                "\tLEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry xjd ON xjsp.inquiry_id = xjd.id and xjd.deleted = 0\n" +
                "\tLEFT JOIN sourcingcenter.i_sourcingcenter_t_quotation_goods stqg ON stqg.inquiry_goods_id = xjsp.id and stqg.deleted=0\n" +
                "\tLEFT JOIN operation1center.i_operation1center_t_platform_grant_user zckh on zckh.organization_id = xjd.organization_id\n" +
                "\tLEFT JOIN sourcingcenter.i_sourcingcenter_t_inquiry_goods_clarify cqlb on cqlb.inquiry_goods_id =xjsp.id and cqlb.deleted=0\n" +
                "\twhere xjsp.deleted=0 ") ;
        getQueryCondition(good,sql);
        return  sql;
    }

    public static List<Map<String,Object>> getOrderList (InquiryGoodDto good, Integer limit , Integer size){
        StringBuffer sql = new StringBuffer();
        sql.append("\tSELECT\n" +
                "  distinct \n"
                );
        sql.append(getCommonConditionSql());
        sql = getCommonOrderSql(good, sql);

        if (limit != null && size != null){
            sql.append( " limit "+limit +","+size);
        }
        log.info("sql:{}",sql);
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql.toString());
        return maps;
    }


    public static StringBuffer getQueryCondition(InquiryGoodDto good,StringBuffer sql){
        if (org.apache.commons.lang3.StringUtils.isNotBlank(good.getInquiry_id())){
            sql.append(" and xjd.id like concat('%','").append(good.getInquiry_id()).append("','%')");
        }
        if (org.apache.commons.lang3.StringUtils.isNotBlank(good.getInquiry_goods_id())){
            sql.append(" and xjsp.id ='"+good.getInquiry_goods_id()+ "'");
        }
        if (org.apache.commons.lang3.StringUtils.isNotBlank(good.getOrganization_company_name())){
            sql.append (" and xjd.organization_company_name like  '%"+ good.getOrganization_company_name()+"%'");
        }
        if (StringUtils.isNotBlank(good.getInquiry_goodName())){
            sql.append (" and xjsp.name like  '%"+ good.getInquiry_goodName()+"%'");
        }
        //是否已经澄清
        if (StringUtils.isNotBlank(good.getInquiry_goodClarifyCount())){
            if(StringUtils.equals(good.getInquiry_goodClarifyCount(),"1")){
                sql.append (" and cqlb.id != null and cqlb.id != '' ");
            }else{
                sql.append (" and (cqlb.id = null or cqlb.id ='') ");
            }
        }
        if (StringUtils.isNotBlank(good.getInquiry_goodSpec())){
            sql.append (" and xjsp.spec like  '%"+ good.getInquiry_goodSpec()+"%'");
        }
        if (StringUtils.isNotBlank(good.getInquiry_goodModel())){
            sql.append (" and xjsp.model like  '%"+ good.getInquiry_goodModel()+"%'");
        }
        if (StringUtils.isNotBlank(good.getInquiry_goodBrand())){
            sql.append (" and xjsp.brand like  '%"+ good.getInquiry_goodBrand()+"%'");
        }
        if (StringUtils.isNotBlank(good.getInquiry_goods_status())){
            sql.append (" and xjsp.inquiry_goods_status like  '%"+ good.getInquiry_goods_status()+"%'");
        }
        if (StringUtils.isNotBlank(good.getQuotation_goods_status())){
            sql.append (" and stqg.quotation_goods_status like  '%"+ good.getQuotation_goods_status()+"%'");
        }
        if (StringUtils.isNotBlank(good.getRelease_startTime())){
            sql.append (" and xjd.release_time >= '"+good.getRelease_startTime()+"'");
        }
        if (StringUtils.isNotBlank(good.getRelease_endTime())){
            sql.append (" and xjd.release_time <= '"+good.getRelease_endTime()+"'");
        }
        if (StringUtils.isNotBlank(good.getExpire_startTime())){
            sql.append (" and xjd.expire >= '"+good.getExpire_startTime()+"'");
        }
        if (StringUtils.isNotBlank(good.getExpire_endTime())){
            sql.append (" and xjd.expire <= '"+good.getExpire_endTime()+"'");
        }
        if (StringUtils.isNotBlank(good.getInquiry_goods_status())){
            sql.append (" and xjsp.inquiry_goods_status = '"+good.getQuotation_goods_status()+"'");
        }
        if (StringUtils.isNotBlank(good.getQuotation_goods_status())){
            sql.append (" and stqg.quotation_goods_status = '"+good.getQuotation_goods_status()+"'");
        }
        if (StringUtils.isNotBlank(good.getClarify_startTime())){
            sql.append (" and stqg.createdTime <= '"+good.getClarify_startTime()+"'");
        }
        if (StringUtils.isNotBlank(good.getClarify_endTime())){
            sql.append (" and stqg.createdTime <= '"+good.getClarify_endTime()+"'");
        }


        return  sql;
    }

    public static String getCommonConditionSql(){
        return  "xjd.id inquiry_id, \n" +
                "\txjsp.quote_count, \n" +
                "\txjsp.id, \n" +
                "\tzckh.member_id, \n" +
                "\tzckh.organization_id, \n" +
                "\txjd.quote_total_strategy,\n" +
                "\txjd.quote_multiple_purchases,\n" +
                "\txjd.quote_hermetic_strategy,\n" +
                "\txjsp.inquiry_goods_status,\n" +
                "\txjd.expire,\n" +
                "\txjd.name inquiry_name,\n" +
                "\txjd.organization_company_name,\n" +
                "\txjd.organization_id,\n" +
                "\txjsp.platform_category_name,\n" +
                "\txjsp.name ,\n" +
                "\txjsp.memo ,\n" +
                "\txjsp.brand,\n" +
                "\txjsp.model,\n" +
                "\txjsp.spec,\n" +
                "\txjsp.unit,\n" +
                "\txjd.createdTime";
    }
    public static String getCommonClarifyConditionSql(){
        return  "\t xjd.id inquiry_id, \n" +
                "\txjsp.id, \n" +
                "\txjsp.quote_count, \n" +
                "\txjd.quote_total_strategy,\n" +
                "\txjd.quote_multiple_purchases,\n" +
                "\txjd.quote_hermetic_strategy,\n" +
                "\txjsp.inquiry_goods_status,\n" +
                "\txjd.expire,\n" +
                "\txjd.name,\n" +
                "\txjd.organization_company_name,\n" +
                "\txjsp.platform_category_name,\n" +
                "\tif(xjd.organization_id != null , JSON_UNQUOTE(json_extract( xjd.organization_id, '$.id' )), '')customerNo,"+
                "\txjsp.name goodName,\n" +
                "\txjsp.brand,\n" +
                "\txjsp.model,\n" +
                "\txjsp.spec,\n" +
                "\txjsp.unit,\n" +
                "\tCOUNT(cqlb.id) clarify_count,\n" +
                "\tmin(cqlb.createdTime) clarify_time,\n" +
                "\txjd.createdTime";
    }

    public static int getInquiryGoodClarifyCount(InquiryGoodDto good) {

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" xjsp.id ");
        sql = getCommonClarifyOrderSql(good, sql);
        getQueryCondition(good,sql);
        sql.append(" group by xjsp.id");
        log.info("sql:{}",sql);
        return jdbcTemplate.queryForList(sql.toString()).size();

    }


    public static List<Map<String, Object>> getInquiryGoodClarifyNote(InquiryGoodDto good, Integer start, Integer pageSize) {

        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append(getCommonClarifyConditionSql());
        sql = getCommonClarifyOrderSql(good, sql);
        sql.append(" group by xjsp.id");
        if (start != null && pageSize != null){
            sql.append( " limit "+start +","+pageSize);
        }
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql.toString());
        return maps;
    }
}
